using System;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;

public class AbstractDAO
{
    /// <summary>
    /// cac bien thanh phan thuc hien thao tac truy xuat CSDL
    /// </summary>
    protected String sql;//bien giu vai tro la cau lenh truy van
    protected SqlConnection objConnection;
    protected SqlCommand objCommand;
    protected SqlDataAdapter objDataAdapter;
    protected SqlDataReader objDataReader;
    //////////////////////////////////////////////////////////////////////////
    //////////////////////////////////////////////////////////////////////////
    // Pointer to an external unmanaged resource.
    private IntPtr handle;
    // Other managed resource this class uses.
    private Component component = new Component();
    // Track whether Dispose has been called.
    private bool disposed = false;
    public AbstractDAO(IntPtr handle)
    {
        this.handle = handle;
    }
    /// <summary>
    /// cac ham thuc hien thao tao huy(lay trong MSDN_khong can quan tam)
    /// </summary>
    public void Dispose()
    {
        Dispose(true);
        // This object will be cleaned up by the Dispose method.
        // Therefore, you should call GC.SupressFinalize to
        // take this object off the finalization queue 
        // and prevent finalization code for this object
        // from executing a second time.
        GC.SuppressFinalize(this);
    }
    // Dispose(bool disposing) executes in two distinct scenarios.
    // If disposing equals true, the method has been called directly
    // or indirectly by a user's code. Managed and unmanaged resources
    // can be disposed.
    // If disposing equals false, the method has been called by the 
    // runtime from inside the finalizer and you should not reference 
    // other objects. Only unmanaged resources can be disposed.
    public void Dispose(bool disposing)
    {
        // Check to see if Dispose has already been called.
        if (!this.disposed)
        {
            // If disposing equals true, dispose all managed 
            // and unmanaged resources.
            if (disposing)
            {
                // Dispose managed resources.
                component.Dispose();
            }

            // Call the appropriate methods to clean up 
            // unmanaged resources here.
            // If disposing is false, 
            // only the following code is executed.
            CloseHandle(handle);
            handle = IntPtr.Zero;
        }
        disposed = true;
    }

    // Use interop to call the method necessary  
    // to clean up the unmanaged resource.
    [System.Runtime.InteropServices.DllImport("Kernel32")]
    private extern static Boolean CloseHandle(IntPtr handle);

    // Use C# destructor syntax for finalization code.
    // This destructor will run only if the Dispose method 
    // does not get called.
    // It gives your base class the opportunity to finalize.
    // Do not provide destructors in types derived from this class.
    ~AbstractDAO()
    {
        // Do not re-create Dispose clean-up code here.
        // Calling Dispose(false) is optimal in terms of
        // readability and maintainability.
        Dispose(false);
    }
    //////////////////////////////////////////////////////////////////////////
    //////////////////////////////////////////////////////////////////////////
    /// <summary>
    /// truyen duong dan toi thuc muc chua database de thuc thi ket noi
    /// </summary>
    /// <param name="strPath"></param>
    public AbstractDAO(String strPath)
    {
        try
        {
            string objConnectionString = strPath;

            objConnection = new SqlConnection(objConnectionString);
        }
        catch (System.Exception e)
        {
            throw new Exception(e.Message, e.InnerException);
        }
    }
    /// <summary>
    /// Add parameter cho cau lenh truy van
    /// </summary>
    /// <param name="Name"></param> 
    /// <param name="Type"></param>
    /// <param name="Size"></param>
    /// <param name="Value"></param>
    public void AddParameter(string Name, SqlDbType Type, int Size, object Value)
    {
        try
        {
            objCommand.Parameters.Add(Name, Type, Size).Value = Value;
        }
        catch (System.Exception OleDbExceptionErr)
        {
            throw new System.Exception(OleDbExceptionErr.Message,
            OleDbExceptionErr.InnerException);
        }
    }
    /// <summary>
    /// Add parameter, thuc hien viec lay gia tri duoc tra ve tu store
    /// </summary>
    /// <param name="Name"></param>
    /// <param name="Type"></param>
    /// <param name="Size"></param>
    /// <param name="Value"></param>
    public void AddParameterWithDirection(string Name, SqlDbType Type, int Size)
    {
        try
        {
            SqlParameter param = objCommand.CreateParameter();
            param.ParameterName = Name;
            param.Direction = ParameterDirection.Output;
            param.SqlDbType = Type;
            param.Size = Size;
            objCommand.Parameters.Add(param);
        }
        catch (System.Exception OleDbExceptionErr)
        {
            throw new System.Exception(OleDbExceptionErr.Message,
            OleDbExceptionErr.InnerException);
        }
    }
    public object GetParamValue(string Name)
    {
        return objCommand.Parameters[Name].Value;
    }
    /// <summary>
    /// mo ket noi
    /// </summary>
    public void OpenConnection()
    {
        try
        {
            objConnection.Open();
        }
        catch (SqlException ExceptionErr)
        {
            throw new Exception(ExceptionErr.Message, ExceptionErr.InnerException);
        }
        catch (InvalidOperationException InvalidOperationExceptionErr)
        {
            throw new Exception(InvalidOperationExceptionErr.Message, InvalidOperationExceptionErr.InnerException);
        }
    }
    /// <summary>
    /// dong ket noi
    /// </summary>
    public void CloseConnection()
    {
        objConnection.Close();
    }
    /// <summary>
    /// Khoi tao cau lenh truy van
    /// </summary>
    public void InitializeCommand()
    {
        if (objCommand == null)
        {
            try
            {
                objCommand = new SqlCommand(sql, objConnection);
                //See if this is a stored procedure
                if (!sql.ToUpper().StartsWith("SELECT ")
                    && !sql.ToUpper().StartsWith("INSERT ")
                    && !sql.ToUpper().StartsWith("UPDATE ")
                    && !sql.ToUpper().StartsWith("DELETE "))
                {
                    objCommand.CommandType = CommandType.StoredProcedure;
                }
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
        }
    }
    public void InitializeDataAdapter()
    {
        try
        {
            objDataAdapter = new SqlDataAdapter();
            objDataAdapter.SelectCommand = objCommand;//lua chon cau lenh
        }
        catch (System.Exception e)
        {
            throw new Exception(e.Message, e.InnerException);
        }
    }
    /// <summary>
    /// do du lieu vao dataset
    /// </summary>
    /// <param name="daoDataSet"></param>
    /// <param name="TableName"></param>
    public void FillDataSet(ref DataSet daoDataSet, String TableName)
    {
        try
        {
            InitializeCommand();
            InitializeDataAdapter();
            objDataAdapter.Fill(daoDataSet, TableName);
        }
        catch (System.Exception e)
        {
            throw new Exception(e.Message, e.InnerException);
        }
    }

    public void CleanUpObject()
    {
        objCommand.Dispose();
        objCommand = null;
        objDataAdapter.Dispose();
        objDataAdapter = null;
    }
    /// <summary>
    /// do du lieu vao dataTable
    /// </summary>
    /// <param name="daoDataSet"></param>
    /// <param name="TableName"></param>
    public void FillDataTable(ref DataTable oDataTable)
    {
        try
        {
            InitializeCommand();
            InitializeDataAdapter();
            objDataAdapter.Fill(oDataTable);
        }
        catch (System.Exception e)
        {
            throw new Exception(e.Message, e.InnerException);
        }
        finally
        {
            objCommand.Dispose();
            objCommand = null;
            objDataAdapter.Dispose();
            objDataAdapter = null;
        }
    }
    /// <summary>
    /// thuc thi cau truy van
    /// </summary>
    /// <returns>thuc thi xong, dong ket noi tra ve 1 bao thanh cong</returns>
    public Boolean ExecuteStoredProcedure()
    {
        try
        {
            OpenConnection();
            if (objCommand.ExecuteNonQuery() != 0)
            {
                CloseConnection();
                return true;
            }
            else
            {
                CloseConnection();
                return false;//that bai
            }
        }
        catch (System.Exception e)
        {
            throw new Exception(e.Message, e.InnerException);
        }
    }
}